≫ Adding documents to a table

Adding documents to a real-time table

If you're looking for information on adding documents to a plain table, please refer to the section on adding data from external storages.

Adding documents in real-time is supported only for Real-Time and percolate tables. The corresponding SQL command, HTTP endpoint, or client functions insert new rows (documents) into a table with the provided field values. It's not necessary for a table to exist before adding documents to it. If the table doesn't exist, Manticore will attempt to create it automatically. For more information, see Auto schema.

You can insert a single or multiple documents with values for all fields of the table or just a portion of them. In this case, the other fields will be filled with their default values (0 for scalar types, an empty string for text types).

Expressions are not currently supported in INSERT, so values must be explicitly specified.

The ID field/value can be omitted, as RT and PQ tables support auto-id functionality. For numeric-ID tables, you can also use 0 as the id value to force automatic ID generation. Rows with duplicate IDs will not be overwritten by INSERT. Instead, you can use REPLACE for that purpose.

For tables created with id uuid, pass an explicit UUID as a quoted string or omit id to generate one automatically. Explicit values must match xxxxxxxx-xxxx-Vxxx-Wxxx-xxxxxxxxxxxx, where each x is a hexadecimal digit, V is the version (1 through 8), and W is the variant (8, 9, a, or b). Uppercase hexadecimal letters are accepted and normalized to lowercase. Unlike numeric IDs, 0 does not request automatic UUID generation.

When using the HTTP JSON protocol, you have two different request formats to choose from: a common Manticore format and an Elasticsearch-like format. Both formats are demonstrated in the examples below.

Additionally, when using the Manticore JSON request format, keep in mind that the doc node is required, and all the values should be provided within it.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
📋

General syntax:

INSERT INTO <table name> [(column, ...)]
VALUES (value, ...)
[, (...)]
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products(title) VALUES ('Crossbody Bag with Tassel');
INSERT INTO products VALUES (0,'Yellow bag', 4.95);
‹›
Response
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)

Adding documents to replicated tables

When working with replicated tables, you must use a special syntax to ensure that write operations are properly propagated to all nodes in the cluster.

For all write operations (INSERT, REPLACE, DELETE, TRUNCATE, UPDATE) on replicated tables, you must:

  • In SQL: Use the cluster_name:table_name format instead of just the table name
  • In JSON: Include the cluster property along with the table property

If you don't use the correct syntax, the operation will fail with an error.

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO posts:weekly_table(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO posts:weekly_table VALUES (0,'Yellow bag', 4.95);
‹›
Response
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)

Auto schema

NOTE: Auto schema requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

Manticore features an automatic table creation mechanism, which activates when a specified table in the insert or replace query doesn't yet exist. This mechanism is enabled by default. To disable it, set auto_schema = 0 in the Searchd section of your Manticore config file.

By default, all text values in the VALUES clause are considered to be of the text type, except for values representing valid email addresses, which are treated as the string type.

If you attempt to INSERT/REPLACE multiple rows with different, incompatible value types for the same field, auto table creation will be canceled, and an error message will be returned. However, if the different value types are compatible, the resulting field type will be the one that accommodates all the values. Some automatic data type conversions that may occur include:

  • mva -> mva64
  • uint -> bigint -> float (this may cause some precision loss)
  • string -> text

The auto schema mechanism does not support creating tables with vector fields (fields of type float_vector) used for KNN (K-Nearest Neighbors) similarity search. To use vector fields in your table, you must explicitly create the table with a schema that defines these fields. If you need to store vector data in a regular table without KNN search capability, you can store it as a JSON array using the standard JSON syntax, for example: INSERT INTO table_name (vector_field) VALUES ('[1.0, 2.0, 3.0]').

Also, the following formats of dates will be recognized and converted to timestamps while all other date formats will be treated as strings:

  • %Y-%m-%dT%H:%M:%E*S%Z
  • %Y-%m-%d'T'%H:%M:%S%Z
  • %Y-%m-%dT%H:%M:%E*S
  • %Y-%m-%dT%H:%M:%s
  • %Y-%m-%dT%H:%M
  • %Y-%m-%dT%H

Keep in mind that the /bulk HTTP endpoint does not support automatic table creation (auto schema). Only the /_bulk (Elasticsearch-like) endpoint and the SQL interface support this feature.

‹›
  • SQL
  • JSON
📋
MySQL [(none)]> drop table if exists t; insert into t(i,f,t,s,j,b,m,mb) values(123,1.2,'text here','test@mail.com','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777)); desc t; select * from t;
‹›
Response
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.42 sec)
--------------
insert into t(i,f,t,j,b,m,mb) values(123,1.2,'text here','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777))
--------------
Query OK, 1 row affected (0.00 sec)
--------------
desc t
--------------
+-------+--------+----------------+
| Field | Type   | Properties     |
+-------+--------+----------------+
| id    | bigint |                |
| t     | text   | indexed stored |
| s     | string |                |
| j     | json   |                |
| i     | uint   |                |
| b     | bigint |                |
| f     | float  |                |
| m     | mva    |                |
| mb    | mva64  |                |
+-------+--------+----------------+
8 rows in set (0.00 sec)
--------------
select * from t
--------------
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| id                  | i    | b             | f        | m    | mb                          | t         | s             | j          |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| 5045949922868723723 |  123 | 1099511627776 | 1.200000 | 1,2  | 1099511627776,1099511627777 | text here | test@mail.com | {"a": 123} |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
1 row in set (0.00 sec)

Auto ID

Manticore provides automatic ID generation for documents inserted or replaced into a real-time or Percolate table. The generator produces a unique numeric value with the guarantees below, but it should not be considered an auto-incrementing sequence.

The generated ID value is guaranteed to be unique under the following conditions:

  • The server_id value of the current server is in the range of 0 to 127 and is unique among nodes in the cluster, or it uses the default value generated from the MAC address as a seed
  • The system time does not change for the Manticore node between server restarts
  • The average auto-ID generation rate between two server starts stays below about 16 million IDs per second

The auto ID generator creates a 64-bit integer with the following layout:

  • Bits 0 to 23 are a counter that is incremented on every call to the auto ID generator
  • Bits 24 to 55 store the server start time in seconds, encoded as (unix_timestamp_at_start - 2019-05-01 00:00:00 UTC)
  • Bits 56 to 62 store the server_id (the value is masked to the range 0..127)

This layout ensures that generated IDs are unique among cluster nodes and that data inserted into different cluster nodes does not create collisions. This is particularly important when working with replicated tables, as it guarantees that auto-generated IDs are unique across all nodes in the replication cluster.

Important: the 24-bit counter is not a hard limit on the total number of documents you can insert during one server run. You can insert more than 16,777,216 documents after startup; the IDs will still keep increasing and remain unique for that running process. The ~16 million IDs per second rule matters for uniqueness across restarts: after a restart, the time-based part must advance far enough so that newly generated IDs do not overlap with IDs created before the restart.

As a result, the first ID from the generator used for auto ID is NOT 1 but a larger number. Additionally, the document stream inserted into a table might have non-sequential ID values if inserts into other tables occur between calls, as the ID generator is singular in the server and shared between all its tables.

For numeric-ID tables, this integer is the public document ID. For UUID-ID tables, Manticore encodes it as a canonical UUIDv8 string; clients see only the UUID.

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products VALUES (0,'Yello bag', 4.95);
select * from products;
‹›
Response
+---------------------+-----------+---------------------------+
| id                  | price     | title                     |
+---------------------+-----------+---------------------------+
| 1657860156022587404 | 19.850000 | Crossbody Bag with Tassel |
| 1657860156022587405 |  4.950000 | Yello bag                 |
+---------------------+-----------+---------------------------+

UUID_SHORT multi-ID generation

CALL UUID_SHORT(N)

The CALL UUID_SHORT(N) statement allows for generating N unique 64-bit IDs in a single call without inserting any documents. It is particularly useful when you need to pre-generate IDs in Manticore for use in other systems or storage solutions. For example, you can generate auto-IDs in Manticore and then use them in another database, application, or workflow, ensuring consistent and unique identifiers across different environments.

‹›
  • Example
Example
📋
CALL UUID_SHORT(3)
‹›
Response
+---------------------+
| uuid_short()        |
+---------------------+
| 1227930988733973183 |
| 1227930988733973184 |
| 1227930988733973185 |
+---------------------+

Bulk adding documents

You can insert not just a single document into a real-time table, but as many as you'd like. It's perfectly fine to insert batches of tens of thousands of documents into a real-time table. However, it's important to keep the following points in mind:

  • The larger the batch, the higher the latency of each insert operation
  • The larger the batch, the higher the indexation speed you can expect
  • You might want to increase the max_packet_size value to allow for larger batches
  • Normally, each batch insert operation is considered a single transaction with atomicity guarantee, so you will either have all the new documents in the table at once or, in case of failure, none of them will be added. See more details about an empty line or switching to another table in the "JSON" example.

Note that the /bulk HTTP endpoint does not support automatic creation of tables (auto schema). Only the /_bulk (Elasticsearch-like) endpoint and the SQL interface support this feature. The /_bulk (Elasticsearch-like) HTTP endpoint allows the table name to include the cluster name in the format cluster_name:table_name.

/_bulk endpoint accepts document IDs in the same format as Elasticsearch, and you can also include the id within the document itself:

{ "index": { "table": "products", "_id": "1" } }
{ "title": "Crossbody Bag with Tassel", "price": 19.85 }

or

{ "index": { "table": "products" } }
{ "title": "Crossbody Bag with Tassel", "price": 19.85, "id": "1" }

For an RT table declared with id uuid, /bulk reads UUIDs from id. /_bulk reads them from metadata _id or the document id. Both endpoints can omit the ID to generate a UUID automatically.

Chunked transfer in /bulk

The /bulk (Manticore mode) endpoint supports Chunked transfer encoding. You can use it to transmit large batches. It:

  • reduces peak RAM usage, lowering the risk of OOM
  • decreases response time
  • allows you to bypass max_packet_size and transfer batches much larger than the maximum allowed value of max_packet_size (128MB), for example, 1GB at a time.

Bulk import

For faster large batch loads into a local real-time table, Manticore Search can write INSERT rows directly to a disk chunk and publish that chunk when the transaction commits. This avoids building the batch in a RAM chunk first. Rows remain invisible until the chunk is published, and a failed operation or ROLLBACK leaves the table unchanged.

It supports both row-wise and columnar tables, including full-text fields, numeric attributes, strings, JSON, MVA/MVA64, and float vectors with KNN indexes.

Table names are case-insensitive here. For example, SET bulk_import=Products and bulk_import=products both select the table stored as products; tables whose names differ only by letter case cannot be distinguished.

SQL

Enable the mode for the current SQL session, run one or more INSERT statements against the bound table, and commit:

SET bulk_import=products;
INSERT INTO products(id,title,price) VALUES
  (101,'Crossbody Bag with Tassel',19.85),
  (102,'Microfiber Sheet Set',19.99);
INSERT INTO products(id,title,price) VALUES
  (103,'Pet Hair Remover Glove',7.99);
COMMIT;
SET bulk_import=0;

Run SET bulk_import=<table> before BEGIN and before any uncommitted write on that connection. It reserves the selected table for that session: searches remain available, while another bulk import or an ordinary write to the same table is rejected until the reservation is released. BEGIN and START TRANSACTION are allowed but not required and have no effect in this mode.

COMMIT publishes the rows collected since the previous COMMIT or ROLLBACK as one disk chunk. ROLLBACK discards those rows. In both cases, bulk import remains enabled so you can start another batch. Run SET bulk_import=0 to discard any uncommitted rows, disable bulk import, and release this connection's reservation. Writes to the table resume after the reservation is released. Closing the connection does the same. Autocommit cannot be changed while bulk import is enabled.

HTTP /bulk

Add bulk_import=<table> to a Manticore /bulk request. The request body remains standard newline-delimited JSON (NDJSON), and each operation must be insert or create for the bound table:

printf '%s\n' \
  '{"insert":{"table":"products","id":101,"doc":{"title":"Crossbody Bag with Tassel","price":19.85}}}' \
  '{"insert":{"table":"products","id":102,"doc":{"title":"Microfiber Sheet Set","price":19.99}}}' |
curl -sS -X POST \
  -H 'Content-Type: application/x-ndjson' \
  --data-binary @- \
  'http://localhost:9308/bulk?bulk_import=products'

A line can also be a bare document: the table comes from bulk_import=<table>, and id is one of the fields:

printf '%s\n' \
  '{"id":101,"title":"Crossbody Bag with Tassel","price":19.85}' \
  '{"id":102,"title":"Microfiber Sheet Set","price":19.99}' |
curl -sS -X POST \
  -H 'Content-Type: application/x-ndjson' \
  --data-binary @- \
  'http://localhost:9308/bulk?bulk_import=products'

Every field of a bare document is a column, including a field named doc; a document without id gets an auto-generated ID. A line is an operation when its only field is named as a /bulk operation (insert, create, index, replace, update or delete) and holds an object with table or index; any other line is a bare document. Both forms can be mixed in one request. A /bulk request without bulk_import still requires the operation form.

In a request body, an empty line ends and publishes the current batch; the end of the request publishes the final batch. Manticore sends the HTTP response after processing all batches in that request. If a later batch fails, batches published earlier in the same request remain searchable. To publish the entire request atomically as one disk chunk, do not include empty lines. The response contains one aggregate bulk result for each published batch rather than one result per document.

For a typical import, send the complete NDJSON body in one request as shown above. Close the HTTP connection when the import is complete to release the table for other writes.

The endpoint supports chunked transfer encoding, so it can process bodies larger than max_packet_size without buffering the whole request.

Shared HTTP bulk import

Add shared next to bulk_import=<table> when many concurrent requests, for example a log shipper that sends every flush over a new connection, load one table. All such requests feed one table-wide pool of indexers. The pool does not belong to a connection: it lives across requests, connections and reconnects until it is committed, cancelled with PURGE, the table is dropped, or the daemon stops.

curl -sS -X POST -H 'Content-Type: application/x-ndjson' --data-binary @part1.ndjson \
  'http://localhost:9308/bulk?bulk_import=products&shared=4'

Each line must be an insert or create for the target table, or a bare document as described above. The first document creates the pool and starts its indexers. A successful data response ("shared_state":"staged") means the documents are staged, not searchable: neither the end of a request, nor a closed connection, nor HTTP chunk boundaries publish anything.

From the first document until the pool is committed, fails or is cancelled, the pool reserves the table, as a private bulk import does: searches keep working, while ordinary writes (INSERT, REPLACE, UPDATE, DELETE) and another bulk import of the same table fail with an error saying that the table is locked. Closing connections does not release the table. SHOW LOCKS lists a reserved table with the lock type write_allow_readers. The first document of a new pool is rejected with shared_precondition and the same error while another bulk import holds the table, while the table is locked with LOCK TABLES … READ, or while an ordinary write to it is running.

A request without documents that contains an empty line commits the pool:

printf '\n' | curl -sS -X POST -H 'Content-Type: application/x-ndjson' --data-binary @- \
  'http://localhost:9308/bulk?bulk_import=products&shared'

The commit closes the pool to new data, stops the indexers that received no data, waits until every other indexer has built its part, attaches one disk chunk per such indexer, removes the pool and answers "shared_state":"published". If other data requests are still running, the commit answers HTTP 409 with shared_busy and the pool stays open; repeat the commit after the writers finish. A commit without a pool answers 409 with shared_no_pool, because a commit never creates a pool; a pool that received no data publishes no chunk. Empty lines in a request with documents, including lines of only spaces, are ignored, so a data request never publishes. LF and CRLF both end a line.

The number of indexers, and so of new disk chunks, is set by shared=N in the request that creates the pool. With a bare shared, the daemon uses the INDEXER_RT_BULK_SHARED_POOL_SIZE environment variable of searchd, otherwise one indexer. N is limited to the range from 1 to the number of worker threads minus 2 (at least 1): a smaller value, or one that is not a number, gives one indexer, and a larger value gives the maximum. Only the request that creates the pool reads N; requests that join the open pool, and the commit, ignore it. Data is spread over the indexers in batches of about 32 KiB, in turn. More indexers shorten the build after the commit but use more CPU and memory while loading. A good starting point is about two thirds of the worker threads, for example 8 indexers with 12 threads; with more, the indexers compete with the incoming requests for CPU and disk, and the load gets slower. Each indexer needs roughly 200 MB of RAM while loading.

Every response is one JSON object with errors, error, error_code and shared_state:

shared_state HTTP error_code Meaning
staged 200 data is in the pool
published 200 the pool was committed
busy 409 shared_busy commit while other data requests are active
closing 409 shared_closing the pool is being published
no_pool 409 shared_no_pool commit, or a data request with shared_join, without a pool
rejected 400, or 403 without write permission shared_precondition invalid request, e.g. no write permission or a table that is locked when the pool is created
failed 400, 409 or 500 shared_failed the load failed, see below

current_line counts the documents of the request; a commit reports 1. On an error skipped_lines equals current_line, although the batches of the request already handed to the indexers stay in the pool.

If a connection breaks or times out in the middle of a request body, or the request is killed with KILL, only that request fails: its last unwritten part (less than 32 KiB) is dropped, data already handed to the indexers stays, and the pool remains open, so the client can resend the request. An invalid document (malformed JSON, unknown column, another table, an operation other than insert/create) or a failed write to an indexer fails the whole load: its indexers are stopped and the table is released at once, that request gets 400 for an invalid document or 500 for a failed write, every later request gets 409 with the first error, and the next commit returns this error with 500 and removes the failed pool and its staging files; PURGE … OPTION clear_sessions=1 removes it as well. Only then can a new load start.

A failed request and a failed load get the same answer, failed with shared_failed (400, or 500 for a killed request), so the response alone does not tell them apart. The next request does: staged means the pool is still open, while 409 with shared_failed means the load failed.

A pool lives only in the memory of searchd. If the daemon stops or crashes during a load, or the pool is cancelled, its staged documents are gone, and the next data request would start a new pool that holds only the later documents. To notice this, add shared_join to every data request after the first staged reply: such a request only joins the open pool and never creates one, so without a pool it answers 409 with shared_no_pool, and the load can be repeated from the start.

The pool does not end when its clients do. If they are gone before the commit, for example because a log shipper was stopped or crashed, the pool stays open and keeps the table reserved, so ordinary writes keep failing. Release the table in one of two ways: commit the pool as shown above, which publishes the documents staged so far, or cancel it, which discards them:

PURGE BULK_IMPORT FROM TABLE products OPTION clear_sessions=1;

The cancel stops the indexers, releases the table and removes the staging files; chunks published earlier stay in the table. A commit answers 409 with shared_busy while a data request of a vanished client still counts as running, until that request times out; the cancel does not wait for it. Once the table is released, SHOW LOCKS no longer lists it, and a new Shared load can start. See PURGE BULK_IMPORT for details.

Elasticsearch /_bulk

The Elasticsearch-compatible /_bulk endpoint does not support direct-to-disk bulk_import; use SQL or Manticore /bulk.

Duplicate document IDs

Within one direct-to-disk batch, the first row for a numeric document ID remains visible and later rows with the same ID are logically removed. For SQL, a batch consists of the rows staged before COMMIT or ROLLBACK. For Manticore /bulk, each group published at an empty line, table change, or request EOF is a separate batch. In a Shared load, the data of each indexer is a separate batch, and its chunks are attached one after another when the pool is committed. If attaching one of them fails, the chunks attached before it stay in the table and the commit answers 500; loading the same documents again is safe for explicit IDs, because a published row replaces any row with the same ID, while documents with auto-generated IDs are added again.

When Manticore publishes a disk chunk to the target table, a row in that chunk replaces any existing row with the same ID. This also applies to IDs published by an earlier HTTP batch. As a result, retrying a previously published batch replaces its rows, while duplicates within the retried batch still keep the first row. In a Manticore /bulk request, the create operation, for example:

{"create":{"table":"products",...}}

behaves like insert; it does not fail merely because the ID already exists.

Memory and disk usage

A direct-to-disk load does not collect its documents in the memory of searchd. Each INSERT statement, or each part of a /bulk request body (about 32 KiB when the request has the shared parameter), is converted to CSV and passed to an indexer process over a pipe right away. indexer builds the chunk the same way as a plain table: it collects data in memory up to its mem_limit, fixed at the default 128 MB, flushes it to temporary files and, after the last row, merges them into the disk chunk. So the memory a load needs does not grow with its size: each indexer process takes its mem_limit plus its write buffers and the process itself, roughly 200 MB in total. A SET bulk_import session or a /bulk request runs one indexer, a load with shared=N runs N; add the statement or request part being processed.

As with a plain table (see inplace_enable), the intermediate data in the temporary files is comparable in size to the finished chunk. Both are written to the table's staging directory, next to the table files, so at the peak the disk that holds the table needs free space for about twice the size of the new chunk. When the chunk is published, its files are renamed into the table without copying. See Staging files and cleanup.

Staging files and cleanup

Manticore removes the current staging directory after a normal COMMIT, ROLLBACK, mode disable, or session close. A Shared pool keeps its staging directories until it is committed or cancelled; closing connections does not remove them. A daemon or host crash can leave an abandoned staging directory behind. A later direct-to-disk load creates a new uniquely named directory and does not reuse or attach files left by the crashed load.

Use the following query to inspect all direct-to-disk staging entries for a table:

SELECT file, normalized, size
FROM products.@files
OPTION format='bulk_import';

The result recursively lists files and directories below the table's direct-to-disk staging root. Directories and other non-regular entries have a reported size of 0.

After confirming that no direct-to-disk load is active for the table, remove its entire staging root with PURGE BULK_IMPORT:

PURGE BULK_IMPORT FROM TABLE products;

While the table has an open Shared pool, PURGE is refused; PURGE BULK_IMPORT FROM TABLE products OPTION clear_sessions=1 cancels the pool first, stops its indexers, releases the table and then removes the staging root. While the pool is attaching its chunks, PURGE is refused; repeat it after the commit finishes. On Windows, PURGE … OPTION clear_sessions=1 or DROP TABLE while a data request is still writing to the pool can answer failed to remove bulk staging root: the pool is already cancelled, but its stopped indexers keep their files open for a moment; repeat the statement.

PURGE requires an existing local real-time table that is not in a replication cluster. It removes only direct-to-disk staging state and does not change the table schema or indexed rows. It succeeds as a no-op when the staging root is absent. In configless mode, DROP TABLE also removes the table's direct-to-disk staging root and cancels its Shared pool; a daemon shutdown cancels every Shared pool. Chunks published earlier stay in the table.

Current limitations

  • The target must be one existing, unfrozen local real-time table that is not a replication-cluster member. Distributed, sharded, replicated, percolate, and plain tables are not supported.
  • SQL supports only INSERT. Manticore /bulk accepts insert, create and bare documents; index, replace, update, and delete are rejected.
  • A document ID is converted as in a regular INSERT, including attr_autoconv_strict. A row without an ID, or with an ID that converts to 0, gets an auto-generated ID; the response does not return generated IDs, and a repeated load adds such rows again. UUID document IDs are not supported.
  • Static builds are not supported.
  • The platform-specific executable (indexer on Linux, indexer.exe on Windows) must be in the same directory as the running searchd executable. Manticore Search resolves only that sibling path and does not search PATH. Use the executable from the same installation as searchd to ensure compatibility. If it is absent, unreadable, or cannot be started, only bulk import fails; normal startup and regular insertion remain available.

The assisted loader uses the csvpipe source. Its command defaults to /bin/cat on Linux and - on Windows, where - reads from the indexer's standard input. Set INDEXER_RT_BULK_CSV_PIPE_COMMAND in the searchd environment to override only this command. The override does not select another source or feed; the command must work with the platform's input feed and pass CSV rows to the indexer. An unsupported command causes the assisted load to fail.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋

For bulk insert, simply provide more documents in brackets after VALUES(). The syntax is:

INSERT INTO <table name>[(column1, column2, ...)] VALUES(value1[, value2 , ...]), (...)

The optional column name list allows you to explicitly specify values for some of the columns present in the table. All other columns will be filled with their default values (0 for scalar types, empty string for string types).

For example:

INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85), ('microfiber sheet set', 19.99), ('Pet Hair Remover Glove', 7.99);
‹›
Response
Query OK, 3 rows affected (0.01 sec)

Expressions are currently not supported in INSERT, and values should be explicitly specified.

Inserting multi-value attributes (MVA) values

Multi-value attributes (MVA) are inserted as arrays of numbers.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO products(title, sizes) VALUES('shoes', (40,41,42,43));

Inserting JSON

JSON value can be inserted as an escaped string (via SQL or JSON) or as a JSON object (via the JSON interface).

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO products VALUES (1, 'shoes', '{"size": 41, "color": "red"}');
Data creation and modification Adding rules to a percolate table
Last modified: October 15, 2026